﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using TestItemsSummary.Models;
using TestItemsSummary.Common;

namespace TestItemsSummary.Helpers
{
    /// <summary>
    /// 与数据库交互的类
    /// </summary>
    public class SqlHelper
    {
        SqlCommand sqlComm;
        SqlConnection sqlConn;
        SqlDataAdapter sqlDA;
        static SqlConnecParamsEntity sEntity=null;
        static SqlHelper()
        {
           sEntity = BusinessProcess.DeserializeFromXml<SqlConnecParamsEntity>(AppDomain.CurrentDomain.BaseDirectory + "SqlConnectParamConfig.cfg");
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        void Open()
        {
            try
            {
                sqlConn = new SqlConnection(string.Format("server={0};database={1};User Id={2};Password={3}",sEntity.Server,sEntity.Database,sEntity.UserId,sEntity.Password));
                sqlConn.Open();
            }
            catch
            {
                this.Close();//异常关闭数据库连接
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        void Close()
        {
            sqlConn?.Close();
        }
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql">查询字符串</param>
        /// <returns></returns>
        public DataTable GetTable(string sql)
        {
            DataTable dt = new DataTable();//返回的数据表
            DataSet ds = new DataSet();//数据表集
            try
            {
                this.Open();
                using (sqlComm = new SqlCommand(sql, sqlConn))
                {
                    using (sqlDA = new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        dt = ds.Tables[0];
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return dt;
        }
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql">查询字符串</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public DataTable GetTable(string sql,string tableName)
        {
            DataTable dt = new DataTable();//返回的数据表
            DataSet ds = new DataSet();//数据表集
            try
            {
                this.Open();
                using (sqlComm = new SqlCommand(sql, sqlConn))
                {
                    using (sqlDA = new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        dt = ds.Tables[tableName];
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return dt;
        }
        /// <summary>
        /// 执行增删改查操作
        /// </summary>
        /// <param name="sql">查询字符串</param>
        public bool ExecuteNonQuery(string sql)
        {
            int affectLines = 0;//受影响的行数
            bool result = false;//返回的结果
            try
            {
                Open();
                using (sqlComm=new SqlCommand(sql,sqlConn))
                {
                   affectLines= sqlComm.ExecuteNonQuery();
                   result= affectLines > 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return result;
        }
        /// <summary>
        /// 获取第一行第一列的值(例如统计数量Count)
        /// </summary>
        /// <typeparam name="T">泛型参数类型</typeparam>
        /// <param name="sql">查询字符串</param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql)
        {
            T result = default(T);
            try
            {
                Open();
                using (sqlComm=new SqlCommand(sql,sqlConn))
                {
                    result = (T)Convert.ChangeType(sqlComm.ExecuteScalar(),typeof(T));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return result;
        }
        /// <summary>
        /// 执行存储过程《针对于返回是状态码和状态信息的存储过程》
        /// </summary>
        /// <param name="proceName">存储过程名</param>
        /// <param name="sqlParameters">存储过程参数</param>
        /// <returns></returns>
        public string ExecuteStoredProcedure(string proceName, SqlParameter[] sqlParameters)
        {
            string retMsg = string.Empty;//数据库返回的状态码和状态信息
            try
            {
                Open();
                using (sqlComm=new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    sqlComm.ExecuteNonQuery();
                    retMsg = sqlComm.Parameters["@StateCode"].Value.ToString() + "|" + sqlComm.Parameters["@StateMsg"].Value.ToString();// OK|操作成功！
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return retMsg;
        }
        /// <summary>
        /// 执行存储过程并返回数据表
        /// </summary>
        /// <param name="proceName">存储过程名称</param>
        /// <param name="sqlParameters">存储过程参数集</param>
        /// <param name="dt">存储过程返回的数据表</param>
        /// <returns></returns>
        public string ExecuteStoredProcedureAndReturnDatatable(string proceName, SqlParameter[] sqlParameters,out DataTable dt)
        {
            string retMsg = string.Empty;//数据库返回的状态码和状态信息
            DataSet ds = new DataSet();
            try
            {
                Open();
                using (sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    using (sqlDA=new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[ds.Tables.Count-1];
                        }
                        else
                        {
                            dt = new DataTable();
                        }
                       
                        retMsg = sqlComm.Parameters["@StateCode"].Value.ToString() + "|" + sqlComm.Parameters["@StateMsg"].Value.ToString();// OK|操作成功！
                    }
                    //sqlComm.ExecuteNonQuery();
                   
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return retMsg;
        }
        /// <summary>
        /// 执行存储过程并返回数据表集
        /// </summary>
        /// <param name="proceName">存储过程名称</param>
        /// <param name="sqlParameters">存储过程参数</param>
        /// <param name="dataTables">返回数据表集</param>
        /// <returns></returns>
        public string ExecuteStoredProcedureAndReturnDatatables(string proceName, SqlParameter[] sqlParameters, out DataTable[] dataTables,int ArrayLength)
        {
            string retMsg = string.Empty;//数据库返回的状态码和状态信息
            DataSet ds = new DataSet();
            dataTables = new DataTable[ArrayLength];
            try
            {
                Open();
                using (sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    using (sqlDA = new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            for (int i = 0; i < ds.Tables.Count; i++)
                            {
                                dataTables[i] = ds.Tables[i];
                            }
                           // dt = ds.Tables[ds.Tables.Count - 1];
                        }
                        else
                        {
                            dataTables = new DataTable[] { };
                            //dt = new DataTable();
                        }

                        retMsg = sqlComm.Parameters["@StateCode"].Value.ToString() + "|" + sqlComm.Parameters["@StateMsg"].Value.ToString();// OK|操作成功！
                    }
                    //sqlComm.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return retMsg;
        }
        /// <summary>
        /// 执行存储过程并返回数据表集《重载》
        /// </summary>
        /// <param name="proceName">存储过程名称</param>
        /// <param name="sqlParameters">存储过程参数</param>
        /// <param name="dataTables">返回数据表集</param>
        /// <returns></returns>
        public string ExecuteStoredProcedureAndReturnDatatables(string proceName, SqlParameter[] sqlParameters,List<DataTable> dataTables)
        {
            string retMsg = string.Empty;//数据库返回的状态码和状态信息
            DataSet ds = new DataSet();
            try
            {
                Open();
                using (sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    using (sqlDA = new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            for (int i = 0; i < ds.Tables.Count; i++)
                            {
                                dataTables.Add(ds.Tables[i]);
                            }
                        }
                        else
                        {
                            dataTables = new List<DataTable>();
                        }

                        retMsg = sqlComm.Parameters["@StateCode"].Value.ToString() + "|" + sqlComm.Parameters["@StateMsg"].Value.ToString();// OK|操作成功！
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return retMsg;
        }
        /// <summary>
        /// 执行存储过程并返回特定变量的值
        /// </summary>
        /// <param name="proceName">存储过程名称</param>
        /// <param name="scalarName">变量名</param>
        /// <param name="sqlParameters">存储过程参数</param>
        /// <returns></returns>
        public string ExecuteStoredProcedure(string proceName,string scalarName, SqlParameter[] sqlParameters)
        {
            string scalarValue = string.Empty;//数据库返回的信息
            try
            {
                Open();
                using (sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    sqlComm.ExecuteNonQuery();
                    scalarValue = sqlComm.Parameters[scalarName].Value.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return scalarValue;
        }
    }
}
